require 'openssl'
require 'write_xlsx'
module MakeExcel
  def self.project_excel(project_id)
    project = Project.find(project_id)
    company  = Company.find(project.certification_company)
    excel_name = project.name + "下载公示表.xlsx"
    url = "/home/deploy/public/"+ excel_name
    #url = "/Users/yudagong/"+ excel_name
    project_user = ProjectUser.where(project_id: project_id, role: 1).first
    user = User.find(project_user.user_id)
    project_app_id = project.app_id.nil? ? "" : project.app_id
    workbook = WriteXLSX.new("/home/deploy/public/"+excel_name)
    # workbook = WriteXLSX.new("/Users/yudagong/"+excel_name)
    ############# 第一个表 ###########
    worksheet1 = workbook.add_worksheet('企业及项目信息一览表')
    # format_tile = workbook.add_format(:center_across => 7)
    worksheet1.set_row(0, 40)
    worksheet1.set_row(1, 28)
    format_title1 = workbook.add_format(
      :bold   => 1,
      :size   => 18,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format_title2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format_body1 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'left',
    )
    format_body2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format_weiba1 = workbook.add_format(
      :size   => 8,
      :valign => 'vcentre',
      :align  => 'left',
    )
    worksheet1.set_column('A:A', 15)
    worksheet1.set_column('B:D', 7)
    worksheet1.set_column('E:E', 20)
    worksheet1.set_column('F:G', 7)
    worksheet1.merge_range('A1:G1', '企业及项目信息一览表', format_title1)
    worksheet1.set_row(1, 28)
    worksheet1.merge_range('A2:G2', '企业信息', format_title2)
    worksheet1.set_row(2, 28)
    worksheet1.write('A3', '企业名称', format_body1)
    worksheet1.merge_range('B3:G3', company.company_name, format_body2)
    worksheet1.set_row(3, 28)
    worksheet1.write('A4', '企业注册地', format_body1)
    worksheet1.merge_range('B4:D4', CommonApi::cover_city(company.register_address), format_body2)
    worksheet1.write('E4', '注册地区编码', format_body1)
    worksheet1.merge_range('F4:G4', company.register_address, format_body2)
    worksheet1.set_row(4, 28)
    worksheet1.write('A5', '企业注册资金', format_body1)
    worksheet1.merge_range('B5:D5', "", format_body2)
    worksheet1.write('E5', '联系人及联系方式', format_body1)
    worksheet1.merge_range('F5:G5', "", format_body2)
    worksheet1.set_row(5, 28)
    worksheet1.merge_range('A6:G6', '项目信息', format_title2)
    worksheet1.set_row(6, 28)
    worksheet1.write('A7', '项名称', format_body1)
    worksheet1.merge_range('B7:E7', project.name, format_body2)
    worksheet1.write('F7', '项目编码', format_body1)
    worksheet1.write('G7', project_app_id, format_body2)
    worksheet1.set_row(7, 28)
    worksheet1.write('A8', '项目分类', format_body1)
    worksheet1.merge_range('B8:D8', CommonApi.cove_project_type_name(project.project_type), format_body2)
    worksheet1.write('E8', '施工许可证编号', format_body1)
    worksheet1.merge_range('F8:G8', "", format_body2)
    worksheet1.set_row(8, 28)
    worksheet1.write('A9', '项目所在地', format_body1)
    worksheet1.merge_range('B9:E9', project.project_address, format_body2)
    worksheet1.write('F9', '项目状态', format_body1)
    worksheet1.write('G9', CommonApi.cove_project_status_to_name(project.project_status), format_body2)
    worksheet1.set_row(9, 28)
    worksheet1.write('A10', '项目负责人', format_body1)
    worksheet1.merge_range('B10:D10', user.name, format_body2)
    worksheet1.write('E10', '联系方式', format_body1)
    worksheet1.merge_range('F10:G10', user.phone, format_body2)
    worksheet1.set_row(10, 15)
    worksheet1.write('A11', "制表时间", format_weiba1)
    worksheet1.write('F11', "制表时间", format_weiba1)


    format2_title1 = workbook.add_format(
      :bold   => 1,
      :size   => 18,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format2_title2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format2_body1 = workbook.add_format(
      :size   => 12,
      :valign => 'vcentre',
      :align  => 'left',
    )
    format2_body2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format2_weiba1 = workbook.add_format(
      :size   => 8,
      :valign => 'vcentre',
      :align  => 'left',
    )
    worksheet2 = workbook.add_worksheet('工人信息表')
    worksheet2.set_row(0, 35)
    worksheet2.merge_range('A1:Q1', '工人信息表', format2_title1)
    worksheet2.merge_range('A2:Q2', '项目名称：'+ project.name+'项目编码：'+project_app_id+'制表日期：年月日', format2_title2)
    project_user = ProjectUser.where("project_id = ? and upload_status = 2", project_id)                             
    worksheet2.write('A3', '序号', format2_body2)
    worksheet2.write('B3', '姓名', format2_body2)
    worksheet2.write('C3', '所属企业名称', format2_body2)
    worksheet2.write('D3', '企业统一社会信用代码', format2_body2)
    worksheet2.write('E3', '班组编号', format2_body2)
    worksheet2.write('F3', '职务（班组长或者工人）', format2_body2)
    worksheet2.write('G3', '身份证号码', format2_body2)
    worksheet2.write('H3', '发证机关', format2_body2)
    worksheet2.write('I3', '工种', format2_body2)
    worksheet2.write('J3', '工人类型（管理人员或工人）', format2_body2)
    worksheet2.write('K3', '民族', format2_body2)
    worksheet2.write('L3', '籍贯', format2_body2)
    worksheet2.write('M3', '政治面貌', format2_body2)
    worksheet2.write('N3', '联系方式', format2_body2)
    worksheet2.write('O3', '文化程度', format2_body2)    
    worksheet2.write('P3', '紧急联系人', format2_body2)
    worksheet2.write('Q3', '联系方式', format2_body2)
    sheet2_biaoji = 4
    project_user.each do|pu|
      user = User.find(pu.user_id)
      user_detail = UserDetail.where(user_id: pu.user_id).first
      if user_detail.company_id.nil?
        company_name = ""
        company_credit_code = ""
        team_code = ""
      else
        company = Company.find(user_detail.company_id)
        project_team = ProjectTeam.where(company_id: company.id, project_id: project_id).first
        company_name = company.company_name
        company_credit_code = company.credit_code
      end
      if project_team.nil?
        team_code = ""
      else
        team_code = project_team.team_code
      end
      line = sheet2_biaoji.to_s
      a = 'A'+line
      worksheet2.write(a, line, format2_body2)
      worksheet2.write('B'+line, user.name, format2_body2)
      worksheet2.write('C'+line, company_name, format2_body2)
      worksheet2.write('D'+line, company_credit_code, format2_body2)
      worksheet2.write('E'+line, team_code, format2_body2)
      worksheet2.write('F'+line, '工人', format2_body2)
      worksheet2.write('G'+line, user.identity_card, format2_body2)
      worksheet2.write('H'+line, user_detail.licensing_organizations, format2_body2)
      worksheet2.write('I'+line, CommonApi.cove_job_to_name(user_detail.job_id), format2_body2)
      worksheet2.write('J'+line, CommonApi.cover_worker_type_to_name(user_detail.worker_type), format2_body2)
      worksheet2.write('K'+line, '汉族', format2_body2)
      worksheet2.write('L'+line, CommonApi::cover_city(user_detail.native_place), format2_body2)
      worksheet2.write('M'+line, CommonApi.cover_politics_type_to_name(user_detail.politics_type), format2_body2)
      worksheet2.write('N'+line, user.phone, format2_body2)
      worksheet2.write('O'+line, CommonApi.cover_culture_leve_to_name(user_detail.culture_leve), format2_body2)    
      worksheet2.write('P'+line, '', format2_body2)
      worksheet2.write('Q'+line, '', format2_body2)
      sheet2_biaoji = sheet2_biaoji + 1
    end
    format3_title1 = workbook.add_format(
      :bold   => 1,
      :size   => 18,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format3_title2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format3_body1 = workbook.add_format(
      :size   => 12,
      :valign => 'vcentre',
      :align  => 'left',
    )
    format3_body2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format3_weiba1 = workbook.add_format(
      :size   => 8,
      :valign => 'vcentre',
      :align  => 'left',
    )
    worksheet3 = workbook.add_worksheet('工人进退场及合同统计表')
    worksheet3.set_row(0, 35)
    worksheet3.merge_range('A1:Q1', '工人进退场及合同统计表', format3_title1)
    worksheet3.merge_range('A2:Q2', '项目名称：'+ project.name+'项目编码：'+project_app_id+'制表日期：年月日', format3_title2)
    project_user = ProjectUser.where("project_id = ? and upload_status = 2", project_id)                             
    worksheet3.write('A3', '序号', format3_body2)
    worksheet3.write('B3', '姓名', format3_body2)
    worksheet3.write('C3', '所属企业名称', format3_body2)
    worksheet3.write('D3', '企业统一社会信用代码', format3_body2)
    worksheet3.write('E3', '班组编号', format3_body2)
    worksheet3.write('F3', '职务（班组长或者工人）', format3_body2)
    worksheet3.write('G3', '身份证号码', format3_body2)
    worksheet3.write('H3', '进场日期', format3_body2)
    worksheet3.write('I3', '退场日期', format3_body2)
    worksheet3.write('J3', '合同期限类型', format3_body2)
    worksheet3.write('K3', '合同编号', format3_body2)
    worksheet3.write('L3', '生效/失效日期', format3_body2)
    worksheet3.write('M3', '工人签字', format3_body2)
    worksheet3.write('N3', '备注', format3_body2)
    sheet3_biaoji = 4
    project_user.each do|pu|
      user = User.find(pu.user_id)
      user_detail = UserDetail.where(user_id: pu.user_id).first
      if user_detail.company_id.nil?
        company_name = ""
        company_credit_code = ""
        team_code = ""
      else
        company = Company.find(user_detail.company_id)
        project_team = ProjectTeam.where(company_id: company.id, project_id: project_id).first
        company_name = company.company_name
        company_credit_code = company.credit_code
      end
      if project_team.nil?
        team_code = ""
      else
        team_code = project_team.team_code
      end
      line = sheet3_biaoji.to_s
      a = 'A'+line
      contract_begin_time = pu.contract_begin_time.nil?? "" : pu.contract_begin_time
      contract_delete_time = pu.contract_delete_time.nil?? "" : pu.contract_delete_time
      contract_time = contract_begin_time + "-" + contract_delete_time
      worksheet3.write(a, line, format2_body2)
      worksheet3.write('B'+line, user.name, format2_body2)
      worksheet3.write('C'+line, company_name, format2_body2)
      worksheet3.write('D'+line, company_credit_code, format2_body2)
      worksheet3.write('E'+line, team_code, format2_body2)
      worksheet3.write('F'+line, '工人', format2_body2)
      worksheet3.write('G'+line, user.identity_card, format2_body2)
      worksheet3.write('H'+line, pu.join_time, format2_body2)
      worksheet3.write('I'+line, pu.left_time, format2_body2)
      worksheet3.write('J'+line, CommonApi.cover_contract__type_to_name(pu.contract_type), format2_body2)
      worksheet3.write('K'+line, pu.contract_code, format2_body2)
      worksheet3.write('L'+line, contract_time , format2_body2)
      worksheet3.write('M'+line, '', format2_body2)
      worksheet3.write('N'+line, '', format2_body2)
      sheet3_biaoji = sheet3_biaoji + 1
    end
    format4_title1 = workbook.add_format(
      :bold   => 1,
      :size   => 18,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format4_title2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format4_body1 = workbook.add_format(
      :size   => 12,
      :valign => 'vcentre',
      :align  => 'left',
    )
    format4_body2 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    format3_weiba1 = workbook.add_format(
      :size   => 8,
      :valign => 'vcentre',
      :align  => 'left',
    )
    worksheet4 = workbook.add_worksheet('工人工资发放表')
    worksheet4.set_row(0, 35)
    worksheet4.merge_range('A1:Q1', '工人工资发放表（    年    月份）', format4_title1)
    worksheet4.merge_range('A2:Q2', '项目名称：'+ project.name+'项目编码：'+project_app_id+'制表日期：年月日', format3_title2)
    project_user = ProjectUser.where("project_id = ? and upload_status = 2", project_id)                             
    worksheet4.write('A3', '序号', format4_body2)
    worksheet4.write('B3', '姓名', format4_body2)
    worksheet4.write('C3', '所属企业名称', format4_body2)
    worksheet4.write('D3', '企业统一社会信用代码', format4_body2)
    worksheet4.write('E3', '班组编号', format4_body2)
    worksheet4.write('F3', '出勤天数', format4_body2)
    worksheet4.write('G3', '工人工资卡号', format4_body2)
    worksheet4.write('H3', '银行代码', format4_body2)
    worksheet4.write('I3', '开户行名称', format4_body2)
    worksheet4.write('J3', '代发银行卡号', format4_body2)
    worksheet4.write('K3', '代发银行代码', format4_body2)
    worksheet4.write('L3', '代发开户行名称', format4_body2)
    worksheet4.write('M3', '应发金额', format4_body2)
    worksheet4.write('N3', '实发金额', format4_body2)
    worksheet4.write('O3', '发放日期', format4_body2)
    worksheet4.write('P3', '工资单编号', format4_body2)
    worksheet4.write('Q3', '工人签字', format4_body2)
    worksheet4.write('R3', '备注', format4_body2)
    sheet4_biaoji = 4
    # project_user.each do|pu|
    #   user = User.find(pu.user_id)
    #   user_detail = UserDetail.where(user_id: pu.user_id).first
    #   if user_detail.company_id.nil?
    #     company_name = ""
    #     company_credit_code = ""
    #     team_code = ""
    #   else
    #     company = Company.find(user_detail.company_id)
    #     project_team = ProjectTeam.where(company_id: company.id, project_id: project_id).first
    #     company_name = company.company_name
    #     company_credit_code = company.credit_code
    #   end
    #   if project_team.nil?
    #     team_code = ""
    #   else
    #     team_code = project_team.team_code
    #   end
    #   line = sheet4_biaoji.to_s
    #   a = 'A'+line
    #   time = Time.now
    #   payroll = Payroll.where("project_id = ? and created_at < ? ", project.id, time.strftime('%Y-%m'))
    #   payroll_details = PayrollDetail.where("")
    #   contract_begin_time = pu.contract_begin_time.nil?? "" : pu.contract_begin_time
    #   contract_delete_time = pu.contract_delete_time.nil?? "" : pu.contract_delete_time
    #   contract_time = contract_begin_time + "-" + contract_delete_time
    #   worksheet4.write(a, line, format3_body2)
    #   worksheet4.write('B'+line, user.name, format3_body2)
    #   worksheet4.write('C'+line, company_name, format3_body2)
    #   worksheet4.write('D'+line, company_credit_code, format3_body2)
    #   worksheet4.write('E'+line, team_code, format3_body2)
    #   worksheet4.write('F'+line, format3_body2)
    #   worksheet4.write('G'+line, user.identity_card, format3_body2)
    #   worksheet4.write('H'+line, pu.join_time, format3_body2)
    #   worksheet4.write('I'+line, pu.left_time, format3_body2)
    #   worksheet4.write('J'+line, CommonApi.cover_contract__type_to_name(pu.contract_type), format3_body2)
    #   worksheet4.write('K'+line, pu.contract_code, format3_body2)
    #   worksheet4.write('L'+line, contract_time , format3_body2)
    #   worksheet4.write('M'+line, '', format3_body2)
    #   worksheet4.write('N'+line, '', format3_body2)
    #   sheet4_biaoji = sheet4_biaoji + 1
    #end
    #### 考勤部分 ### 
    format5_body1 = workbook.add_format(
      :size   => 11,
      :valign => 'vcentre',
      :align  => 'center',
    )
    worksheet5 = workbook.add_worksheet('工人考勤表')

    worksheet5.set_row(0, 35)
    worksheet5.merge_range('A1:Q1', '工人考勤表（    年    月份）', format4_title1)
    worksheet5.merge_range('A2:Q2', '项目名称：'+ project.name+'项目编码：'+project_app_id+'制表日期：年月日', format3_title2)
    project_user = ProjectUser.where("project_id = ?", project_id)
    last_time = Time.now.month - 1
    if (last_time == 1 || last_time == 3 || last_time == 5 || last_time == 7 || last_time == 8 || last_time == 10 || last_time == 12) 
      worksheet5.set_column('H:AL', 3.8 ) 
      month_day_num = 31
      worksheet5.write('A3', '序号', format4_body2)
      worksheet5.write('B3', '姓名', format4_body2)
      worksheet5.write('C3', '所属企业名称', format4_body2)
      worksheet5.write('D3', '班组编号', format4_body2)
      worksheet5.write('E3', '打卡天数', format4_body2)
      worksheet5.write('F3', '缺勤天数', format4_body2)
      worksheet5.write('G3', '缺勤统计', format4_body2)
      worksheet5.write('H3', '1', format5_body1)
      worksheet5.write('I3', '2', format5_body1)
      worksheet5.write('J3', '3', format5_body1)
      worksheet5.write('K3', '4', format5_body1)
      worksheet5.write('L3', '5', format5_body1)
      worksheet5.write('M3', '6', format5_body1)
      worksheet5.write('N3', '7', format5_body1)
      worksheet5.write('O3', '8', format5_body1)
      worksheet5.write('P3', '9', format5_body1)
      worksheet5.write('Q3', '10', format5_body1)
      worksheet5.write('R3', '11', format5_body1)
      worksheet5.write('S3', '12', format5_body1)
      worksheet5.write('T3', '13', format5_body1)
      worksheet5.write('U3', '14', format5_body1)
      worksheet5.write('V3', '15', format5_body1)
      worksheet5.write('W3', '16', format5_body1)
      worksheet5.write('X3', '17', format5_body1)
      worksheet5.write('Y3', '18', format5_body1)
      worksheet5.write('Z3', '19', format5_body1)
      worksheet5.write('AA3', '20', format5_body1)
      worksheet5.write('AB3', '21', format5_body1)
      worksheet5.write('AC3', '22', format5_body1)
      worksheet5.write('AD3', '23', format5_body1)
      worksheet5.write('AE3', '24', format5_body1)
      worksheet5.write('AF3', '25', format5_body1format5_body1)
      worksheet5.write('AG3', '26', format5_body1format5_body1)
      worksheet5.write('AH3', '27', format5_body1format5_body1)
      worksheet5.write('AI3', '28', format5_body1format5_body1)
      worksheet5.write('AJ3', '29', format5_body1format5_body1)
      worksheet5.write('AK3', '30', format5_body1format5_body1)
      worksheet5.write('AL3', '31', format5_body1format5_body1)
      worksheet5.write('AM3', '工人签字', format4_body2)
      worksheet5.write('AN3', '备注', format4_body2)
    else
      month_day_num = 30
      worksheet5.set_column('H:AK', 3.8 ) 
      worksheet5.write('A3', '序号', format4_body2)
      worksheet5.write('B3', '姓名', format4_body2)
      worksheet5.write('C3', '所属企业名称', format4_body2)
      worksheet5.write('D3', '班组编号', format4_body2)
      worksheet5.write('E3', '打卡天数', format4_body2)
      worksheet5.write('F3', '缺勤天数', format4_body2)
      worksheet5.write('G3', '缺勤统计', format4_body2)
      worksheet5.write('H3', '1', format5_body1)
      worksheet5.write('I3', '2', format5_body1)
      worksheet5.write('J3', '3', format5_body1)
      worksheet5.write('K3', '4', format5_body1)
      worksheet5.write('L3', '5', format5_body1)
      worksheet5.write('M3', '6', format5_body1)
      worksheet5.write('N3', '7', format5_body1)
      worksheet5.write('O3', '8', format5_body1)
      worksheet5.write('P3', '9', format5_body1)
      worksheet5.write('Q3', '10', format5_body1)
      worksheet5.write('R3', '11', format5_body1)
      worksheet5.write('S3', '12', format5_body1)
      worksheet5.write('T3', '13', format5_body1)
      worksheet5.write('U3', '14', format5_body1)
      worksheet5.write('V3', '15', format5_body1)
      worksheet5.write('W3', '16', format5_body1)
      worksheet5.write('X3', '17', format5_body1)
      worksheet5.write('Y3', '18', format5_body1)
      worksheet5.write('Z3', '19', format5_body1)
      worksheet5.write('AA3', '20', format5_body1)
      worksheet5.write('AB3', '21', format5_body1)
      worksheet5.write('AC3', '22', format5_body1)
      worksheet5.write('AD3', '23', format5_body1)
      worksheet5.write('AE3', '24', format5_body1)
      worksheet5.write('AF3', '25', format5_body1)
      worksheet5.write('AG3', '26', format5_body1)
      worksheet5.write('AH3', '27', format5_body1)
      worksheet5.write('AI3', '28', format5_body1)
      worksheet5.write('AJ3', '29', format5_body1)
      worksheet5.write('AK3', '30', format5_body1)
      worksheet5.write('AL3', '工人签字', format4_body2)
      worksheet5.write('AM3', '备注', format4_body2)
    end
    sheet4_biaoji = 4
    xuhao = 1
    project_user.each do|pu|

      line = sheet4_biaoji.to_s
      signs = Sign.where("user_id = ? and project_id = ? and year = ? and month = ? ", pu.user_id, pu.project_id, "2019", last_time)
      user = User.find(pu.user_id)
      if pu.upload_project_team.nil?
        company_name = ""
        team_code = ""
      else
        team = ProjectTeam.find(pu.upload_project_team)
        team_code = team.team_code.nil?? "" : team.team_code
        company = Company.find(team.company_id)
        company_name = company.company_name
      end

      worksheet5.write('A'+line, xuhao, format4_body2)
      worksheet5.write('B'+line, user.name, format4_body2)
      worksheet5.write('C'+line, company_name, format4_body2)
      worksheet5.write('D'+line, team_code, format4_body2)
      worksheet5.write('E'+line, signs.count, format4_body2)
      worksheet5.write('F'+line, month_day_num-signs.count, format4_body2)
      worksheet5.write('G'+line, '', format4_body2)
      xuhao = xuhao + 1

      signs.each do |sg|
        if (sg.day == 1)
           worksheet5.write('H'+line, '√', format5_body1)
        elsif (sg.day == 2)
          worksheet5.write('I'+line, '√', format5_body1)
        elsif (sg.day == 3)
          worksheet5.write('J'+line, '√', format5_body1)
        elsif (sg.day == 4)
          worksheet5.write('K'+line, '√', format5_body1)
        elsif (sg.day == 5)
          worksheet5.write('L'+line, '√', format5_body1)
        elsif (sg.day == 6)
          worksheet5.write('M'+line, '√', format5_body1)
        elsif (sg.day == 7)
          worksheet5.write('N'+line, '√', format5_body1)
        elsif (sg.day == 8)
          worksheet5.write('O'+line, '√', format5_body1)
        elsif (sg.day == 9)
          worksheet5.write('P'+line, '√', format5_body1)
        elsif (sg.day == 10)
          worksheet5.write('Q'+line, '√', format5_body1)
        elsif (sg.day == 11)
          worksheet5.write('R'+line, '√', format5_body1)
        elsif (sg.day == 12)
          worksheet5.write('S'+line, '√', format5_body1)
        elsif (sg.day == 13)
          worksheet5.write('T'+line, '√', format5_body1)
        elsif (sg.day == 14)
          worksheet5.write('U'+line, '√', format5_body1)
        elsif (sg.day == 15)
          worksheet5.write('V'+line, '√', format5_body1)  
        elsif (sg.day == 16)
          worksheet5.write('W'+line, '√', format5_body1)
        elsif (sg.day == 17)
          worksheet5.write('X'+line, '√', format5_body1)
        elsif (sg.day == 18)
          worksheet5.write('Y'+line, '√', format5_body1)
        elsif (sg.day == 19)
          worksheet5.write('Z'+line, '√', format5_body1)
        elsif (sg.day == 20)
          worksheet5.write('AA'+line, '√', format5_body1)
        elsif (sg.day == 21)
          worksheet5.write('AB'+line, '√', format5_body1)
        elsif (sg.day == 22)
          worksheet5.write('AC'+line, '√', format5_body1)
        elsif (sg.day == 23)
          worksheet5.write('AD'+line, '√', format5_body1)
        elsif (sg.day == 24)
          worksheet5.write('AE'+line, '√', format5_body1)
        elsif (sg.day == 25)
          worksheet5.write('AF'+line, '√', format5_body1)  
        elsif (sg.day == 26)
          worksheet5.write('AG'+line, '√', format5_body1)
        elsif (sg.day == 27)
          worksheet5.write('AH'+line, '√', format5_body1)
        elsif (sg.day == 28)
          worksheet5.write('AI'+line, '√', format5_body1)
        elsif (sg.day == 29)
          worksheet5.write('AJ'+line, '√', format5_body1)
        elsif (sg.day == 30)
          worksheet5.write('AK'+line, '√', format5_body1)
        elsif (sg.day == 31)
          worksheet5.write('AL'+line, '√', format5_body1)
        end

      end
      sheet4_biaoji = sheet4_biaoji + 1
    end
    workbook.close
    return "http://file2.hanrunkeji.com/" + excel_name
  end
end
